import math
import os
import re
from locale import atof
import xlwt
import sqlutils.dbhelper as dbHelper
import websites.crmeb.suishiapi as crmebApi
from websites.crmeb.suishitype import getGoodsTypeId
import websites.crmeb.suishisql as mySql
from utils.logger import loger

mTitleVlue = ""
mPic1Value = ""
mPic2Value = ""
mPic3Value = ""
mPic4Value = ""
mPic5Value = ""

mStandard1Title = ""
mStandard1Value = ""
mStandard2Title = ""
mStandard2Value = ""
mStandard3Title = ""
mStandard3Value = ""
mStandard4Title = ""
mStandard4Value = ""
mStandard5Title = ""
mStandard5Value = ""
mInfoValue = ""
mGoodsType = 0  # 商品所属分类ID
mGoodsTypeName = ""  # 商品所属分类名称

dateValue = None
positiveIntReg = r"[1-9]\d*"  # 正整数正则
floatValueReg = r"[1-9]\d*\.\d*|0\.\d*[1-9]\d*$"  # 浮点数正则
standardStrReg = r"(([1-9]\d*\.\d*|0\.\d*[1-9]\d*$)元([1-9]\d*)件可售)"  # 规格匹配正则
goodsInventory = 300  # 阿里巴巴库存低于该值时不计入
startBayVlue = "0"  # 起批量
rowCount = 0  # 行控制
maxOrderCount = 3  # 命令次数控制

dbSelectStartIndex = 0  # 数据库查询起始位
dbSelectEndIndex = 200  # 数据库查询结束位
dbSelectAddCount = 200  # 数据库每次查询的增量

spiderDB = "spider"  # 爬虫数据库
spiderTableName = "goodsInfo"  # 商品数据采集存放表

webProductTable = "eb_store_product"  # 商品信息
xlspath = r"C:\Users\da\Desktop\goodsinfo.xls"


def dataParse():
    """
    数据解析
    """
    print("数据处理中......")
    for rowItem in dateValue:
        global rowCount, mPic1Value, mPic2Value, mPic3Value, mPic4Value, mPic5Value, mInfoValue, startBayVlue, mTitleVlue, mGoodsType, mGoodsTypeName
        global mStandard1Title, mStandard1Value, mStandard2Title, mStandard2Value, mStandard3Title, mStandard3Value, mStandard4Title, mStandard4Value, mStandard5Title, mStandard5Value
        mstandard1Arr = {}
        mstandard2Arr = {}
        mstandard3Arr = {}
        mstandard4Arr = {}
        mstandard5Arr = {}
        cleanBuff()

        if (rowCount == 0):
            setXlsHead()
            rowCount = 1

        mTitleVlue = rowItem["title"].replace("批发", "").replace("跨境电商", "").replace("电商", "")
        mGoodsType, mGoodsTypeName = getGoodsTypeId(mTitleVlue)

        if noneCheck(rowItem["startcount"]):
            startBayVlue = re.compile(positiveIntReg).findall(rowItem["startcount"])[0]

        if noneCheck(rowItem["pic1"]):
            mPic1Value = rowItem["pic1"]

        if noneCheck(rowItem["pic2"]):
            mPic2Value = rowItem["pic2"]

        if noneCheck(rowItem["pic3"]):
            mPic3Value = rowItem["pic3"]

        if noneCheck(rowItem["pic4"]):
            mPic4Value = rowItem["pic4"]

        if noneCheck(rowItem["pic5"]):
            mPic5Value = rowItem["pic5"]

        if noneCheck(rowItem["guige1"]):
            mstandard1Arr = standardParse(rowItem["guige1"])
            if mstandard1Arr is not None:
                mStandard1Title = mstandard1Arr[0]
                mStandard1Value = mstandard1Arr[1]

        if noneCheck(rowItem["guige2"]):
            mstandard2Arr = standardParse(rowItem["guige2"])
            if mstandard2Arr is not None:
                mStandard2Title = mstandard2Arr[0]
                mStandard2Value = mstandard2Arr[1]

        if noneCheck(rowItem["guige3"]):
            mstandard3Arr = standardParse(rowItem["guige3"])
            if mstandard3Arr is not None:
                mStandard3Title = mstandard3Arr[0]
                mStandard3Value = mstandard3Arr[1]

        if noneCheck(rowItem["guige4"]):
            mstandard4Arr = standardParse(rowItem["guige4"])
            if mstandard4Arr is not None:
                mStandard4Title = mstandard4Arr[0]
                mStandard4Value = mstandard4Arr[1]

        if noneCheck(rowItem["guige5"]):
            mstandard5Arr = standardParse(rowItem["guige5"])
            if mstandard5Arr is not None:
                mStandard5Title = mstandard5Arr[0]
                mStandard5Value = mstandard5Arr[1]

        if noneCheck(rowItem["msg"]):
            mInfoValue = rowItem["msg"]
        saveXlsData()

    if len(dateValue) >= dbSelectAddCount:
        againSelect()
    else:
        mySql.closeXlsWrite()
        print("数据处理结束")
        print("Excel写入完成")
        inputOrder(0)


def cleanBuff():
    """
    初始化数据
    """
    global mPic1Value, mPic2Value, mPic3Value, mPic4Value, mPic5Value, startBayVlue, mTitleVlue, mGoodsType, mGoodsTypeName, mInfoValue
    global mStandard1Title, mStandard1Value, mStandard2Title, mStandard2Value, mStandard3Title, mStandard3Value, mStandard4Title, mStandard4Value, mStandard5Title, mStandard5Value

    startBayVlue = "0"
    mTitleVlue = ""
    mGoodsType = 0
    mGoodsTypeName = ""
    mInfoValue = ""

    mPic1Value = ""
    mPic2Value = ""
    mPic3Value = ""
    mPic4Value = ""
    mPic5Value = ""

    mStandard1Title = ""
    mStandard1Value = ""
    mStandard2Title = ""
    mStandard2Value = ""
    mStandard3Title = ""
    mStandard3Value = ""
    mStandard4Title = ""
    mStandard4Value = ""
    mStandard5Title = ""
    mStandard5Value = ""


def inputOrder(orderCount):
    """
    输入命令
    """
    if orderCount >= maxOrderCount:
        print("操作结束")
        return

    inputValue = input("Excel文件写入完成,是否上传至数据库（y/n）：")
    if inputValue == "y":
        writeFileForDB()
    elif inputValue == "n":
        print("操作完成")
    else:
        inputOrder(orderCount + 1)


def againSelect():
    """
    循环查询数据库
    """
    global dateValue, dbSelectStartIndex, dbSelectEndIndex
    dbSelectStartIndex = dbSelectStartIndex + dbSelectAddCount
    sql = "select * from " + spiderTableName + "  limit " + str(dbSelectStartIndex) + "," + str(dbSelectAddCount)
    dateValue = dbHelper.select(spiderTableName, sql)
    dataParse()


def saveXlsData():
    """
    保存数据
    """
    mStandardStartTitle = 9  # 规格名称
    mStandardStartValue = 10  # 规格值
    if mStandard1Title != "" or mStandard2Title != "" or mStandard3Title != "" or mStandard4Title != "" or mStandard5Title != "":
        if mPic1Value != "" or mPic2Value != "" or mPic3Value != "" or mPic4Value != "" or mPic5Value != "":
            global rowCount
            mySql.updateItemValue(rowCount, 0, mTitleVlue)

            if mGoodsTypeName is not None and mGoodsType is not None:
                mySql.updateItemValue(rowCount, 1, mGoodsTypeName)
                mySql.updateItemValue(rowCount, 2, mGoodsType)
            else:
                print(mTitleVlue)

            mySql.updateItemValue(rowCount, 3, startBayVlue)
            mySql.updateItemValue(rowCount, 4, mPic1Value)
            mySql.updateItemValue(rowCount, 5, mPic2Value)
            mySql.updateItemValue(rowCount, 6, mPic3Value)
            mySql.updateItemValue(rowCount, 7, mPic4Value)
            mySql.updateItemValue(rowCount, 8, mPic5Value)
            mySql.updateItemValue(rowCount, 19, mInfoValue)

            if mStandard1Title != "" and mStandard1Value != "":
                mySql.updateItemValue(rowCount, mStandardStartTitle, mStandard1Title)
                mySql.updateItemValue(rowCount, mStandardStartValue,
                                      round(float(mStandard1Value) * crmebApi.dbPriceBidPercent, 2))
                mStandardStartTitle = mStandardStartTitle + 2
                mStandardStartValue = mStandardStartValue + 2

            if mStandard2Title != "" and mStandard2Value != "":
                mySql.updateItemValue(rowCount, mStandardStartTitle, mStandard2Title)
                mySql.updateItemValue(rowCount, mStandardStartValue,
                                      round(float(mStandard2Value) * crmebApi.dbPriceBidPercent, 2))
                mStandardStartTitle = mStandardStartTitle + 2
                mStandardStartValue = mStandardStartValue + 2

            if mStandard3Title != "" and mStandard3Value != "":
                mySql.updateItemValue(rowCount, mStandardStartTitle, mStandard3Title)
                mySql.updateItemValue(rowCount, mStandardStartValue,
                                      round(float(mStandard3Value) * crmebApi.dbPriceBidPercent, 2))
                mStandardStartTitle = mStandardStartTitle + 2
                mStandardStartValue = mStandardStartValue + 2

            if mStandard4Title != "" and mStandard4Value != "":
                mySql.updateItemValue(rowCount, mStandardStartTitle, mStandard4Title)
                mySql.updateItemValue(rowCount, mStandardStartValue,
                                      round(float(mStandard4Value) * crmebApi.dbPriceBidPercent, 2))
                mStandardStartTitle = mStandardStartTitle + 2
                mStandardStartValue = mStandardStartValue + 2

            if mStandard5Title != "" and mStandard5Value != "":
                mySql.updateItemValue(rowCount, mStandardStartTitle, mStandard5Title)
                mySql.updateItemValue(rowCount, mStandardStartValue,
                                      round(float(mStandard5Value) * crmebApi.dbPriceBidPercent, 2))
            rowCount = rowCount + 1


def setXlsHead():
    """
    设置xls头部标题
    """
    mySql.updateItemValue(0, 0, "标题")
    mySql.updateItemValue(0, 1, "类型名称")
    mySql.updateItemValue(0, 2, "类型ID")
    mySql.updateItemValue(0, 3, "起批量")
    mySql.updateItemValue(0, 4, "大图1")
    mySql.updateItemValue(0, 5, "大图2")
    mySql.updateItemValue(0, 6, "大图3")
    mySql.updateItemValue(0, 7, "大图4")
    mySql.updateItemValue(0, 8, "大图5")
    mySql.updateItemValue(0, 9, "规格1内容")
    mySql.updateItemValue(0, 10, "规格1价格")
    mySql.updateItemValue(0, 11, "规格2内容")
    mySql.updateItemValue(0, 12, "规格2价格")
    mySql.updateItemValue(0, 13, "规格3内容")
    mySql.updateItemValue(0, 14, "规格3价格")
    mySql.updateItemValue(0, 15, "规格4内容")
    mySql.updateItemValue(0, 16, "规格4价格")
    mySql.updateItemValue(0, 17, "规格5内容")
    mySql.updateItemValue(0, 18, "规格5价格")
    mySql.updateItemValue(0, 19, "详情")


def noneCheck(arg):
    """
    空判断
    """
    if arg is not None and arg != "":
        return True
    else:
        return False


def standardParse(standardStr):
    """
    解析规格
    """
    standardRegArr = re.compile(standardStrReg).findall(standardStr)
    if len(standardRegArr) == 0:
        return None
    standardRegValue = standardRegArr[0][0]
    global startBayVlue, standardTitle, standardPrice
    if int(startBayVlue) > 1:
        standardTitle = standardStr.replace(standardRegValue, "") + " * " + startBayVlue
        standardPrice = atof(standardRegValue.split("元")[0].replace("元", "")) * int(startBayVlue)
    else:
        standardTitle = standardStr.replace(standardRegValue, "")
        standardPrice = atof(standardRegValue.split("元")[0].replace("元", ""))

    standardInventory = int(re.compile(positiveIntReg).findall(standardRegValue.split("元")[1])[0])

    """
    阿里巴巴商品库存大于goodsInventory值时计入
    """
    if standardInventory > goodsInventory:
        return standardTitle, math.ceil(standardPrice)
    else:
        return None


def fileLoad():
    """
    数据库数据和xls文件加载
    """
    if createFile():
        rsheet, wb, ws = mySql.openExcel(xlspath, 0)
        if rsheet is not None and wb is not None and ws is not None:
            rows = len(list(rsheet.get_rows()))
            if rows == 0:
                print("Excel文件打开成功")
                connect = dbHelper.dbconnect()
                if connect is not None:
                    dbHelper.selectDataBase(spiderDB)
                    print("数据库连接成功")
                    global dateValue
                    sql = "select * from " + spiderTableName + "  limit " + str(dbSelectStartIndex) + "," + str(
                        dbSelectAddCount)
                    dateValue = dbHelper.select(spiderTableName, sql)
                    return dateValue
                else:
                    print("数据库连接失败")
                    return None
            else:
                inputValue = input("文件已创建，是否直接存入数据库（y/n）：")
                if inputValue == "y":
                    writeFileForDB()
                else:
                    print("操作结束")
                return None
        else:
            print("xls文件操作异常")
            return None
    else:
        print("操作结束")


def createFile():
    """
    文件不存在时创建文件
    """
    if not os.path.exists(xlspath):
        inputStr = input("数据文件不存在，是否创建文件？(y/n):")
        if inputStr == "y":
            workbook = xlwt.Workbook(encoding='utf-8')
            workbook.add_sheet('sheet_0')
            workbook.save(xlspath)
            print("数据文件创建成功")
            return True
        else:
            return False
    return True


def writeFileForDB():
    """
    数据库操作
    """
    loginStatus = crmebApi.crmebLogin()
    if loginStatus == "成功":
        rsheet, wb, ws = mySql.openExcel(xlspath, 0)
        if rsheet is not None and wb is not None and ws is not None:
            index = 0
            print("打开数据文件成功")
            for rows in rsheet.get_rows():
                if rows[0].value != "" and rows[0].value != "标题":
                    if crmebApi.addGoodsInfo(rows, index) is False:
                        loger.error("第" + str(index) + "条数据插入失败，Name=" + rows[0].value)
                    else:
                        print("第" + str(index) + "条数据插入成功")
                index = index + 1
            print("数据写入完成！共计写入" + str(index) + "条")
        else:
            print("xls文件操作异常")
            return None
    else:
        print(loginStatus)
        return False


if __name__ == '__main__':
    print("1、数据操作；\n2、批量添加评论；\n3、文章插入；")
    getInput = input("请输入序号：")
    if getInput == "1":
        selectDB = fileLoad()
        if selectDB is not None:
            dataParse()
    elif getInput == "2":
        mySql.addAppraiseToTable()
    elif getInput == "3":
        mySql.insertArticle()
    else:
        print("输入有误，结束操作")
